package DataAccess;

import DomainModel.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author Reynier
 * @date 11/10/2013
 *
 */
public class GradesDA {

    private DBManager dbm;
    private Connection conn;
    private PreparedStatement prep;

    // Is it correct for the dbm and conn initializers to go here? Or should
    // they just go in loginUser() ?
    public GradesDA() throws Exception {
        dbm = DBManager.getInstance();
        conn = dbm.getConnection();
    }

    public List<Grade> getStudentGrades(int studentId, int sectionId) throws Exception {
        List<Grade> grades = new LinkedList<Grade>();

        String query = "select ifnull(g.id, 0) as id, ifnull(g.points_earned, 0) as points_earned, ifnull(g.comments, '') as comments, ifnull(g.letter_grade_id, 0) as letter_grade_id, "
                + "   ifnull(lg.letter, '-') letter, cw.class_work_type_id, cwt.name as class_work_type_name, cw.id as class_work_id, \n"
                + "   cw.assigned_date, cw.due_date, cw.max_points, cw.name as class_work_name, cw.weight, cw.section_id \n"
                + "from class_works cw  \n"
                + "left join grades g on g.class_work_id = cw.id and g.student_id = ? \n"
                + "join class_work_types cwt on cwt.id = cw.class_work_type_id \n"
                + "left join letter_grades lg on g.letter_grade_id = lg.id \n"
                + "where cw.section_id = ? and cw.section_id IN \n"
                + "(select ss.section_id from students_sections ss\n"
                + "where ss.student_id and ss.section_id = 13)";
        
        prep = conn.prepareStatement(query);
        prep.setInt(1, studentId);
        prep.setInt(2, sectionId);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                grades.add(this.createGrade(rs));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }

        return grades;
    }

    public List<StudentAndGrade> getSectionGrades(int sectionId, int classWorkId) throws Exception {
        List<StudentAndGrade> studentAndGrades = new LinkedList<StudentAndGrade>();


        String query = "select ss.student_id as studentId, g.id as gradeId, points_earned, comments, class_work_id, \n"
                + "             u.id as userId, user_name,first_name, last_name, email, role_id \n"
                + "                              from users u, students_sections ss left join \n"
                + "                                  ( select * from grades where class_work_id=?) as g  \n"
                + "                                  on ss.student_id = g.student_id \n"
                + "                                  where  u.id = ss.student_id and (ss.section_id=?);";


        prep = conn.prepareStatement(query);
        prep.setInt(1, classWorkId);
        prep.setInt(2, sectionId);
         
        try {
            //System.out.println(":)");
            ResultSet rs = prep.executeQuery();
            //System.out.println(":)");
            while (rs.next()) {
                System.out.println(":(");
                studentAndGrades.add(this.createStudentAndGrade(rs));
            }

            System.out.println(":X");


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            prep.close();
        }

        return studentAndGrades;
    }

    private Grade createGrade(ResultSet rs) throws Exception {
        if (rs == null) {
            return null;
        }

        LetterGrade letterGrade;
        ClassWorkType classWorkType;
        ClassWork classWork;

        // Grade
        int gradeId = rs.getInt("id");
        int pointsEarned = rs.getInt("points_earned");
        String comments = rs.getString("comments");

        // Letter Grade
        int letterGradeId = rs.getInt("letter_grade_id");
        String letter = rs.getString("letter");
        letterGrade = new LetterGrade(letterGradeId, letter);

        // Class Work Type
        int classWorkTypeId = rs.getInt("class_work_type_id");
        String classWorkTypeName = rs.getString("class_work_type_name");
        classWorkType = new ClassWorkType(classWorkTypeId, classWorkTypeName);

        // Class Work
        int classWorkId = rs.getInt("class_work_id");
        String assignedDate = rs.getString("assigned_date");
        String dueDate = rs.getString("due_date");
        int maxPoints = rs.getInt("max_points");
        String classWorkName = rs.getString("class_work_name");
        int weight = rs.getInt("weight");
        int sectionId = rs.getInt("section_id");
        classWork = new ClassWork(classWorkId, classWorkType, sectionId, classWorkName, maxPoints, assignedDate, dueDate, weight);

        return new Grade(gradeId, classWork, pointsEarned, null, letterGrade, comments);
    }

    private StudentAndGrade createStudentAndGrade(ResultSet rs) throws Exception {
        StudentAndGrade studentAndGrade;
        if (rs == null) {
            return null;
        }
        int gradeId, studentId;
        System.out.println("studentId is " + rs.getInt("studentId"));

        studentId = rs.getInt("studentId");

        if (rs.wasNull()) {//if no student registered{
            return null;
        }

        int pointsEarned = rs.getInt("points_earned");
        String comments = rs.getString("comments");
        int classWorkId = rs.getInt("class_work_id");
        String userName = rs.getString("user_name");
        String firstName = rs.getString("first_name");
        String lastName = rs.getString("last_name");
        int userId = rs.getInt("userId");
        String email = rs.getString("email");
        int roleId = rs.getInt("role_id");

        User user = new User(userId, userName, firstName, lastName, email, Role.getRoleById(roleId));




        gradeId = rs.getInt("gradeId");

        if (rs.wasNull()) {//no grade yet
            studentAndGrade = new StudentAndGrade(user, null);
            System.out.println("gradeId was null");

        } else {//get grade information

            System.out.println("gradeId NOT NULL");
            System.out.println("the comments are " + comments);

            Grade grade = new Grade(gradeId, classWorkId, pointsEarned, user, null, comments);

            studentAndGrade = new StudentAndGrade(user, grade);


        }

        return studentAndGrade;
    }

    public boolean insertClassGrades(List<Grade> grades) throws SQLException {
        String query;
        for (Grade grade : grades) {

            if (grade.getPointsEarned() > 0) {
                query = "insert into grades (class_work_id, points_earned, comments, student_id, letter_grade_id)"
                        + " values(?,?,?,?,?)";

                prep = conn.prepareStatement(query);
                prep.setInt(1, grade.getClassWorkId());
                prep.setInt(2, grade.getPointsEarned());
                prep.setString(3, grade.getComments());
                prep.setInt(4, grade.getStudent().getUserId());
                prep.setInt(5, grade.calculateLetterGrade().getLetterGradeId()); //for now I'm setting all grades to A

            } else {
                query = "insert into grades (class_work_id, comments, student_id)"
                        + " values(?,?,?)";

                prep = conn.prepareStatement(query);
                prep.setInt(1, grade.getClassWorkId());
                prep.setString(2, grade.getComments());
                prep.setInt(3, grade.getStudent().getUserId());
            }

            prep.executeUpdate();
        }

        prep.close();

        return true;
    }

    public boolean updateClassGrades(List<Grade> grades) throws SQLException {
        String query;

        for (Grade grade : grades) {
            if (grade.getPointsEarned() > 0) {
                query = "update grades set points_earned=?, comments=?, letter_grade_id=?"
                        + " where id=?";

                prep = conn.prepareStatement(query);
                prep.setInt(1, grade.getPointsEarned());
                prep.setString(2, grade.getComments());
                prep.setInt(3, grade.calculateLetterGrade().getLetterGradeId());
                prep.setInt(4, grade.getGradeId());

            } else {
                query = "update grades set comments=? "
                        + " where id=?";

                prep = conn.prepareStatement(query);
                prep.setString(1, grade.getComments());
                prep.setInt(2, grade.getGradeId());
            }

            prep.executeUpdate();
        }

        prep.close();
        return true;
    }
}
